-- =============================================
-- Procedure: GetLoansDetailedInterestProjectionCountRedBal
-- Purpose:	
-- Author: Garret Stephenson
-- Created: 2013-5-10
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  Dale McFarlane    2014-1-29    Factor in early closed data of loans
-- =====================================================================================
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetLoansDetailedInterestProjectionCountRedBal]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[GetLoansDetailedInterestProjectionCountRedBal]
END
GO

CREATE PROC [dbo].[GetLoansDetailedInterestProjectionCountRedBal]
(
	@from_report_date DATETIME,
	@to_report_date DATETIME
)
AS
BEGIN
	/*set report date: @from = 1st day of the month and @to = last day*/
	SET @from_report_date = dbo.DateFromParts --end of the the month for which the report is required
	(
		YEAR(@from_report_date), 
		MONTH(@from_report_date), 
		1--first day of the month
	)
	SET @to_report_date = dbo.DateFromParts --end of the the month for which the report is required
	(
		YEAR(@to_report_date), 
		MONTH(@to_report_date), 
		DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(YEAR(@to_report_date), MONTH(@to_report_date), 1)), -1))
	)
	
	SELECT
		COUNT(*) AS [Number of Records]
	FROM
		loan_redbal
	WHERE 
		((closed_date IS NULL AND end_date >= @from_report_date) OR(closed_date IS NOT NULL AND closed_date>=@from_report_date)) AND start_date <= @to_report_date
END

GO
